from sqlalchemy import create_engine, Column, Integer, String, BigInteger, DATETIME
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

# 定义base
Base = declarative_base()


# 定义表
class AdsAiBookingOrderNumber(Base):
    __tablename__ = 'ads_ai_booking_order_number'

    order_number = Column(String(765), unique=True, primary_key=True)
    order_number_type = Column(Integer, unique=True)
    booking_id = Column(BigInteger, unique=True)
    delegation_no = Column(String(765))
    ts = Column(DATETIME)


class MysqlManager:
    def __init__(self, host, port, user, password, database):
        self.engine = create_engine(f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}")
        self.Base = Base
        self.Session = sessionmaker(bind=self.engine)

    def query_order_number(self, _order_number):
        """
        查询单号
        :param _order_number: 解释下这张表
        order_number 这个列包含了 箱单号，提单号，委托编号 3种数据，所以验证id是否业务号码用这字段就可以了
        另外： booking_id,  delegation_no 这两个字段是相关的业务号码，暂时没用
        :return: 返回匹配到的值
        """
        session = self.Session()

        search_results = []
        try:
            search_results = session.query(AdsAiBookingOrderNumber).filter(
                AdsAiBookingOrderNumber.order_number == _order_number).all()
        except Exception:
            print(f"_order_number = {_order_number} 查询异常")
        finally:
            session.close()

        len_sr = len(search_results)
        if len_sr == 0:
            return ''
        elif len_sr == 1:
            return search_results[0].order_number
        else:
            raise f"_order_number = {_order_number} 查询结果不唯一"


if __name__ == '__main__':
    mysql_manager = MysqlManager(
        host='10.10.10.81',
        port=19030,
        user='ai',
        password='1kwMB*U1Dl&d!A2I',
        database='ai'
    )
    order_number = '0D326W1MA'
    booking_id_list = mysql_manager.query_order_number(order_number)
    print("booking_id:", booking_id_list)
